package com.dp.appi.dao.impl;

import com.alibaba.fastjson.JSONObject;
import com.dp.appi.dao.BaseDao;
import com.dp.appi.exception.PackageRuntimeException;
import com.dp.appi.mapper.MyColumnMapRowMapper;
import com.dp.appi.util.SYS;
import com.dp.appi.util.StringUtil;
import com.dp.appi.util.oConvertUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.namedparam.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;

import java.util.*;

/**
 * Created by xiaoji on 2015/9/28.
 * 基础数据层
 */
@Component
public class BaseDaoImpl implements BaseDao {

    private static final String TABLE_SCHEMA = "jgzspass";

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public RowMapper<Map<String, Object>> getColumnMapRowMapper() {
        return new MyColumnMapRowMapper();
    }

    @Override
    public Map<String, Object> findOneObject(String sql, Object... args) {
        try {
            return jdbcTemplate.queryForMap(sql, args);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    @Override
    public List<Map<String, Object>> findLists(String sql, Object... args) {
        try {
            return jdbcTemplate.queryForList(sql, args);
        } catch (Exception e) {
            return new ArrayList<>();
        }
    }

    @Override
    public void alterFactorColumn(Map<String, String> param) {
        String type = param.get("type");
        String openid = param.get("openid");

        String isql = "select t1.columns_id from c_consumer_columns as t1 " +
                "inner join c_consumer as t2 on t1.consumer_id=t2.consumer_id " +
                "inner join c_oauth as t3 on t2.consumer_id=t3.consumer_id " +
                "where t3.type=? and t3.openid=?";
        List<Map<String, Object>> lists = findLists(isql, new Object[]{type, openid});

        String s = "";
        if (!lists.isEmpty()) {
            s += "|";
            for (Map<String, Object> m : lists) {
                String columns_id = String.valueOf(m.get("columns_id"));
                s += columns_id + "|";
            }
        }
        String usql = "update c_consumer as t1 inner join c_oauth as t2 on t1.consumer_id=t2.consumer_id " +
                "set t1.factor_column=? where t2.type=? and t2.openid=?";
        getJdbcTemplate().update(usql, new Object[]{s, type, openid});
    }

    @Override
    public Map<String, Object> getConsumer(Map<String, String> param) {
        String type = param.get("type");
        String openid = param.get("openid");

        String userSql = "select t1.* from c_consumer as t1 " +
                "inner join c_oauth as t2 on t1.consumer_id=t2.consumer_id " +
                "where t2.type=? and t2.openid=?";
        Map<String, Object> map = findOneObject(userSql, new Object[]{type, openid});

        return map;
    }

//    @Autowired
//    @Qualifier("jdbcTemplate")
//    private JdbcTemplate jdbcTemplate;

    @Autowired
    @Qualifier("namedParameterJdbcTemplate")
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    /**
     * 使用指定的检索标准检索数据并分页返回数据For JDBC
     */
    public Long getCountForJdbc(String sql) {
        Number number = (Number)this.jdbcTemplate.queryForObject(sql, Long.class);
        return number != null?number.longValue():0L;
//        return this.jdbcTemplate.queryForLong(sql);
    }

    /**
     * 使用指定的检索标准检索数据并分页返回数据For JDBC-采用预处理方式
     *
     */
    public Long getCountForJdbcParam(String sql, Object[] objs) {
        Number number = (Number)this.jdbcTemplate.queryForObject(sql, objs, Long.class);
        return number != null?number.longValue():0L;
//        return this.jdbcTemplate.queryForLong(sql, objs);
    }

    public List<Map<String, Object>> findForJdbc(String sql, Object... objs) {
        return this.jdbcTemplate.queryForList(sql, objs);
    }

    public <T> T queryForObject(String sql, Class<T> requiredType, Object... args) throws DataAccessException {
        try {
            return this.jdbcTemplate.queryForObject(sql, requiredType,args);
        }catch (EmptyResultDataAccessException e){
            return null;
        }
    }


    public Integer executeSql(String sql, List<Object> param) {
        return this.jdbcTemplate.update(sql, param);
    }

    public Integer executeSql(String sql, Object... param) {
        return this.jdbcTemplate.update(sql, param);
    }

    public Integer executeSql(String sql, Map<String, Object> param) {
        return this.namedParameterJdbcTemplate.update(sql, param);
    }

    /**
     * 执行sql语句并且返回主键id
     * @param sql
     * @param param
     * @return
     */
    public Long executeSqlReturnKey(final String sql, Map<String, Object> param) {
        Long keyValue = null;
        KeyHolder keyHolder = new GeneratedKeyHolder();
        SqlParameterSource sqlp  = new MapSqlParameterSource(param);
        this.namedParameterJdbcTemplate.update(sql,sqlp, keyHolder);
        if(oConvertUtils.isNotEmpty(keyHolder.getKey())){
            keyValue = keyHolder.getKey().longValue();
        }
        return keyValue;
    }

    /**
     * 执行sql语句并且返回主键id
     * @param sql
     * @param param
     * @return
     */
    public Long executeSqlReturnID(String sql, Map<String, String> param) {

        Long keyValue = null;
        KeyHolder keyHolder = new GeneratedKeyHolder();

        //TODO bchen 解决数据传递参数不存在情况，为数据库默认值
        SqlParameterSource sqlp  = new MapSqlParameterSource(param);
        ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
        List<SqlParameter> SqlParameterList = NamedParameterUtils.buildSqlParameterList(parsedSql,sqlp);
        for(SqlParameter sqlParameter : SqlParameterList){
            if(null==param.get(sqlParameter.getName())){
                sql = sql.replaceAll(":"+sqlParameter.getName(),"default("+sqlParameter.getName()+")");
            }
        }

        final String sqlS = sql;
        SqlParameterSource sps  = new MapSqlParameterSource(param);
        this.namedParameterJdbcTemplate.update(sqlS,sps, keyHolder);
        if(oConvertUtils.isNotEmpty(keyHolder.getKey())){
            keyValue = keyHolder.getKey().longValue();
        }
        return keyValue;
    }


    public Integer countByJdbc(String sql, Object... param) {
        Number number = (Number)this.jdbcTemplate.queryForObject(sql, param, Integer.class);
        return number != null?number.intValue():0;
//        return this.jdbcTemplate.queryForInt(sql, param);
    }

    public Map<String, Object> findOneForJdbc(String sql, Object... objs) {
        try {
            return this.jdbcTemplate.queryForMap(sql, objs);
        } catch (EmptyResultDataAccessException e) {
            return null;
        }
    }

    /**
     * 获取表字段
     *
     * @param tableName
     * @return
     */
    @Override
    public List<Map<String, Object>> tableField(String tableName) {
        StringBuffer sql = new StringBuffer();
        sql.append("select COLUMN_NAME,DATA_TYPE ");
        sql.append("from information_schema.COLUMNS ");
        sql.append("where table_name =  ? ");
        sql.append("and table_schema = ? ");
        List<Map<String, Object>> ptFieldList = getJdbcTemplate().queryForList(sql.toString(),tableName,TABLE_SCHEMA);
        return ptFieldList;
    }

    /**
     * 系统人员转义
     *
     * @param userIds
     * @param separator
     * @return
     */
    @Override
    public String escapeUser(Object userIds, String separator) {
        if(userIds == null){
            return "";
        }
        String userIdStr = String.valueOf(userIds);
        if("".equals(userIdStr)){
            return "";
        }
        String[] userIdArr = userIdStr.split(separator);
        String userIdParam = "";
        for(int i=0;null != userIdArr && i<userIdArr.length;i++){
            String userId = userIdArr[i];
            if(!"".equals(userId.trim())){
                userIdParam = userIdParam + "'"+ userId + "',";
            }
        }
        if(userIdParam.length() - 1 == userIdParam.lastIndexOf(",")){
            userIdParam = userIdParam.substring(0,userIdParam.length()-1);
        }

        StringBuffer sql = new StringBuffer();
        sql.append("select GROUP_CONCAT(realname) userNames ");
        sql.append("from t_s_base_user where ID in ");
        sql.append("(");
        sql.append(userIdParam);
        sql.append(")");
        return queryForObject(sql.toString(),String.class);
    }

    /**
     * 校对定位点参数
     * @param paramMap
     * @param tableName
     * @return
     */
    public List<Map<String,Object>> assembledParameter(Map<String, Object> paramMap, String tableName){
        List<Map<String, Object>> ptFieldList = tableField(tableName);
        List<Map<String, Object>> ptsFieldList = new ArrayList<>();

        for(Map<String,Object> ptFiledMap : ptFieldList){
            Object column_name = ptFiledMap.get("COLUMN_NAME");
            Object data_type = ptFiledMap.get("DATA_TYPE");
            if(paramMap.containsKey(column_name)) {
                Object value = paramMap.get(column_name);
//                if (null != column_name && null != value) {
                    Map<String, Object> ptsMap = new HashMap<>();
                    ptsMap.put("column_name", column_name);
                    ptsMap.put("column_value", value);
                    ptsMap.put("data_type", data_type);
                    ptsFieldList.add(ptsMap);
//                }
            }
        }
        return ptsFieldList;
    }

    /**
     * 通用修改信息
     *
     * @param paramMap
     * @param pt_table
     * @param condiParam
     * @author bchen
     */
    @Override
    public void upCommField(Map<String, Object> paramMap, String pt_table, Map<String,String> condiParam) {
        List<Map<String, Object>> ptMapList = assembledParameter( paramMap,pt_table);
        if(null == ptMapList || ptMapList.size()==0){
            return;
        }
        StringBuffer sql = new StringBuffer();
        sql.append(" UPDATE ");
        sql.append(pt_table);
        sql.append(" SET ");

        List paramList = new ArrayList();
        for (int i = 0; null != ptMapList && i < ptMapList.size(); i++) {
            Map<String,Object> ptMap = ptMapList.get(i);
            if(i!=0)
                sql.append(" , ");
            sql.append(ptMap.get("column_name"));
            sql.append(" = ? ");
            paramList.add(ptMap.get("column_value"));
        }

        sql.append(" where 1 = 1 ");

        java.util.Iterator it = condiParam.entrySet().iterator();
        while(it.hasNext()){
            java.util.Map.Entry entry = (java.util.Map.Entry)it.next();
            sql.append(" and ");
            sql.append(entry.getKey() );
            sql.append(" = ? ");
            paramList.add(entry.getValue());
        }
        getJdbcTemplate().update(sql.toString(),paramList.toArray());
        if(null==paramMap.get("before_exp")){
            Object exploration_id = paramMap.get("exploration_id");
            String sql2 = "UPDATE p_exploration SET before_exp=NULL,corner=null,distance=null WHERE exploration_id = "+exploration_id;
            getJdbcTemplate().update(sql2);
        }
    }

    /**
     * 通用新增信息
     * @param params
     * @param tableName
     * @author bchen
     * @return
     */
    @Override
    public Long addCommField(Map<String, Object> params, String tableName) {
        List<Map<String, Object>> ptMapList = assembledParameter(params,tableName);
        if(null == ptMapList || ptMapList.size()==0){
            return null;
        }

        String columns = "";
        String question = "";
        Map<String,Object> paramMap = new HashMap<>();
        for (Map<String, Object> ptMap : ptMapList) {
            columns += ptMap.get("column_name");
            columns += ",";
            question += ":"+ptMap.get("column_name")+",";
            paramMap.put(String.valueOf(ptMap.get("column_name")),ptMap.get("column_value"));
        }
        columns = columns.contains(",")?columns.substring(0, columns.lastIndexOf(",")):columns;
        question = question.contains(",")?question.substring(0, question.lastIndexOf(",")):question;

        String sql = "insert into " + tableName + "(" + columns + ") values(" + question + ")";

        Long id = executeSqlReturnKey(sql,paramMap);

        return id;
    }

    /**
     * 通用删除信息
     *
     * @param tableName
     * @param condiParam
     * @author bchen
     * @return
     */
    @Override
    public void delCommField(String tableName, Map<String, String> condiParam) {
        if(condiParam.isEmpty()){
            return;
        }
        StringBuffer sql = new StringBuffer("delete from ");
        sql.append(tableName);
        sql.append(" where ");

        List paramList = new ArrayList();
        java.util.Iterator it = condiParam.entrySet().iterator();
        while(it.hasNext()){
            java.util.Map.Entry entry = (java.util.Map.Entry)it.next();
            sql.append(" and ");
            sql.append(entry.getKey() );
            sql.append(" = ? ");
            paramList.add(entry.getValue());
        }
        getJdbcTemplate().update(sql.toString(),paramList.toArray());
    }

    /**
     * 返回map，避免抛异常
     * @param sql
     * @param args
     * @return
     */
    public Map<String, Object> queryForMap(String sql, Object... args){
        List<Map<String, Object>> results =
                getJdbcTemplate().query(sql, args,
                        new RowMapperResultSetExtractor<Map<String, Object>>(new ColumnMapRowMapper(), 1));
        return DataAccessUtils.uniqueResult(results);
    }

    /**
     * 获取一个项目对象
     *
     * @param tableName  表名
     * @param keyName    主键名称
     * @param key        主键值
     * @param hasDeleted 是否加入deleted字段
     * @return
     */
    @Override
    public Map<String, Object> getObjectByKey(String tableName, String keyName, Long key, boolean hasDeleted) {
        if(key!=null){
            String sql="select * from "+tableName+" where " + keyName +"="+key;
            if(hasDeleted){
                sql+=" and deleted=0";
            }
            return findOneObject(sql);
        }else{
            return null;
        }

    }

    /**
     * 根据uuid获取项目对象
     *
     * @param tableName  表名
     * @param uuid       uuid
     * @param hasDeleted 是否加入deleted字段
     * @return
     */
    @Override
    public Map<String, Object> getObjectByUUID(String tableName, String uuid, boolean hasDeleted) {
        if(uuid!=null) {
            String sql = "select * from " + tableName + " where uuid='" + uuid+"'";
            if (hasDeleted) {
                sql += " and deleted=0";
            }
            return findOneObject(sql);
        }else{
            return null;
        }
    }

    /**
     * 根据表名修改表字段信息
     *
     * @param tableName 表名
     * @param excludeFields    排除不更新字段
     * @param obj       字段属性
     * @param key       主键值
     */
    @Override
    public void updateTableByParam(String tableName, String[] excludeFields, JSONObject obj,Long key) {
        List<Map<String,Object>> list=getFieldsByTableName(tableName);
        String keyName="";//默认是id
        if(list.size() > 0){
            String sql="update "+tableName+" set ";
            List paramList = new ArrayList();
            for(int i=0;i<list.size();i++){
                Map<String,Object> tempMap=list.get(i);
                String tempField=String.valueOf(tempMap.get("Field"));
                String keyStr=String.valueOf(tempMap.get("Key"));
                if("PRI".equals(keyStr)){//主键id
                    keyName=tempField;
                }else{
                    if(!useList(excludeFields,tempField) && obj.get(tempField)!=null && !"".equals(obj.get(tempField))){//排除不更新字段和传入数据空不更新
                        //这里可能需要判断字段类型
                        sql+=tempField+"=?,";
                        paramList.add(obj.get(tempField));
                    }
                }
            }
            if(sql.contains("=") && StringUtil.isNotEmpty(keyName)){//证明加入了set且主键不为空
                sql=sql.substring(0,sql.length()-1);
                sql+=" where "+keyName+"="+key;
                getJdbcTemplate().update(sql,paramList.toArray());
            }
        }
    }

    /**
     * 像指定表名中插入字段
     *
     * @param tableName     表名
     * @param excludeFields 排除插入字段
     * @param obj           参数对象
     * @return
     */
    @Override
    public Long insertTableByParam(String tableName, String[] excludeFields, JSONObject obj) {
        List<Map<String,Object>> list=getFieldsByTableName(tableName);
        String columnsNames="";
        String columnsValues = "";
        Map<String,Object> paramMap = new HashMap<>();
        for(int i=0;i<list.size();i++){
            Map<String,Object> tempMap=list.get(i);
            String tempField=String.valueOf(tempMap.get("Field"));
            String keyStr=String.valueOf(tempMap.get("Key"));

            Object defaultVal=String.valueOf(tempMap.get("Default"));
//            Object defaultVal=tempMap.get("Default");

            if(!"PRI".equals(keyStr)){//主键不加入
                if(!useList(excludeFields,tempField)){//排除不更新字段
                    if(obj.get(tempField)!=null && !"".equals(obj.get(tempField))){
                        columnsNames+=tempField+",";
                        columnsValues += ":"+tempField+",";
                        paramMap.put(tempField,obj.get(tempField));
                    }else{
                        if(!"null".equals(defaultVal) && defaultVal!=null){
                            columnsNames+=tempField+",";
                            columnsValues+="default("+tempField+"),";
                        }
                    }
                }
            }
        }
        columnsNames = columnsNames.contains(",")?columnsNames.substring(0, columnsNames.lastIndexOf(",")):columnsNames;
        columnsValues = columnsValues.contains(",")?columnsValues.substring(0, columnsValues.lastIndexOf(",")):columnsValues;
        String sql = "insert into " + tableName + "(" + columnsNames + ") values(" + columnsValues + ")";
        return this.executeSqlReturnKey(sql,paramMap);
    }
    /**
     * 删除指定表中的记录
     * @param tableName 表名
     * @param keyName 主键名称
     * @param id 主键
     * @param hasDeleted 是否有deleted字段
     */
    @Override
    public void deleteTableRowsByKey(String tableName, String keyName,Object id,boolean hasDeleted){
        if(StringUtil.isNotEmpty(keyName)){//主键为空，不处理
            if(hasDeleted){//有deleted字段deleted为1
                StringBuffer sql = new StringBuffer();
                sql.append(" update ");
                sql.append(tableName);
                sql.append(" set deleted=1 where "+keyName+" = ?");
                getJdbcTemplate().update(sql.toString(),id);
            }else{
                StringBuffer sql = new StringBuffer();
                sql.append(" DELETE FROM ");
                sql.append(tableName);
                sql.append(" where "+keyName+" = ?");
                getJdbcTemplate().update(sql.toString(),id);
            }
        }else{
            throw new PackageRuntimeException(SYS.PACK0003.name());
        }

    }

    /**
     * 数组中是否包含值
     * @param arr
     * @param targetValue
     * @return
     */
    public static boolean useList(String[] arr, String targetValue) {
        return Arrays.asList(arr).contains(targetValue);
    }

    public List<Map<String,Object>> getFieldsByTableName(String tableName){
        String sql2="SHOW COLUMNS FROM "+tableName;
        List<Map<String,Object>> list=findLists(sql2,new Object[]{});
        return list;
    }
}
